import xlrd
import xlwt
import os


class Excel(object):
    cache_list = []
    path = None
    sheetname = None

    def __init__(self, path, sheetname):
        self.cache_list = []
        self.path = None
        self.sheetname = None
        self.path = path
        self.sheetname = sheetname
        try:
            workbook = xlrd.open_workbook(self.path)
            sheet = workbook.sheet_by_name(sheetname)
            if sheet:
                nrows = sheet.nrows
                ncols = sheet.ncols

                for i in range(1, nrows):
                    dic_data = {}
                    for j in range(ncols):
                        title = sheet.cell_value(0, j)
                        value = sheet.cell_value(i, j)
                        dic_data[title] = str(value).replace('\n', '')
                    self.cache_list.append(dic_data)
        except FileNotFoundError as r:
            pass

    def save(self):
        workbook = xlwt.Workbook(encoding='utf-8')
        worksheet = workbook.add_sheet(self.sheetname)

        # header的公约
        max_header = set()
        for rowdict in self.cache_list:
            if isinstance(rowdict, dict):
                max_header = max_header.union(rowdict.keys())

        # 写入header
        cellindex = 0
        for k in max_header:
            worksheet.write(0, cellindex, self.__to_str(k))
            cellindex += 1
        rowindex = 1
        for rowdict in self.cache_list:
            if isinstance(rowdict, dict):
                cellindex = 0
                for k in max_header:
                    v = rowdict.get(k, "")
                    worksheet.write(rowindex, cellindex, self.__to_str(v))
                    cellindex += 1
                rowindex = rowindex + 1
        workbook.save(self.path)

    def getData(self):
        return self.cache_list

    def match_row(self, mrow):
        results = []
        mitems = set(mrow.items())
        for r in self.cache_list:
            # mrow是查询条件,如果mrow是r的子集,那么就匹配上了
            if mitems.issubset(r.items()):
                results.append(r)
        return results

    def update(self, mrow, urow):
        matched_rows = self.match_row(mrow)
        for r in matched_rows:
            r.update(urow)

    def updateOrAdd(self, mrow, urow):
        matched_rows = self.match_row(mrow)
        if matched_rows and matched_rows.__len__() > 0:
            for r in matched_rows:
                r.update(urow)
        else:
            self.add(urow)

    def delete(self, mrow):
        matched_rows = self.match_row(mrow)
        for r in matched_rows:
            self.cache_list.remove(r)

    def add(self, irow):

        self.cache_list.append(irow)

    def __to_str(self, v):

        return str(v)


if __name__ == "__main__":
    filepath = r"E:\gather_tmp\6.xls"
    sheetname = "第一页"
    # # 创建表
    # e = Excel(filepath, sheetname)
    # e.add({"用户名": "张三", "密码": "123456"})
    # e.add({"用户名": "李四", "密码": "789456"})
    # e.save()
    # # 新增
    # e = Excel(filepath, sheetname)
    # e.add({"用户名": "王五", "密码": "asdfds"})
    # e.add({"用户名": "奥巴马", "密码": "789456", "性别": "男"})
    # e.save()
    # 修改
    # e = Excel(filepath, sheetname)
    # e.update({"用户名": "王五"}, {"性别": "未知", "年龄": 20})
    # e.add({"用户名": "奥巴马", "密码": "789456", "性别": "男"})
    # e.save()
    # 删除
    e = Excel(filepath, sheetname)
    e.delete({"用户名": "奥巴马"})
    e.add({"用户名": "齐达内", "密码": "789456", "性别": "男"})
    e.save()
